﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_ConsistencyCheck]
#-- Purpose:		Checks for data errors in the database
#--	Last Update:	04/05/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		[dbo].[spMaint_ConsistencyCheckAll]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_ConsistencyCheck]
(
	@database_name	sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @sCommand varchar(8000), @offline bit, @readonly bit, @major int
DECLARE @tAOAGStat TABLE ([yesno] bit)
DECLARE @sSQL varchar(MAX)

--- Do Not Process tempdb
IF @database_name = 'tempdb'
	RETURN

--- Determine the database state
SELECT	@offline = ISNULL( [dbo].[fnConfig_IsDatabaseOffline] ( @database_name ), 0 ),
		@readonly = ISNULL( [dbo].[fnConfig_IsDatabaseReadOnly] ( @database_name ), 0 ),
		@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int)

--- Update the readonly flag if this is a HADR database (SQL 2012+)
IF @readonly = 1 AND @major > 10
  BEGIN
	IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
		SELECT @sSQL = 'SELECT		TOP 1 1
			FROM		[sys].[availability_replicas]
			WHERE		[replica_server_name] = @@servername
						AND (
							[replica_server_name] = (SELECT [primary_replica] FROM [sys].[dm_hadr_availability_group_states])
							OR (
								[secondary_role_allow_connections] > 0
							)
						)'
	ELSE
		SELECT	@sSQL = 'SELECT 0 WHERE 1 = 0'

	INSERT INTO @tAOAGStat EXEC (@sSQL)
	
	IF @@rowcount > 0
		SET @readonly = 0
  END

--- Only attempt the backup on an ONLINE database
IF @offline = 0 AND @readonly = 0
  BEGIN
	BEGIN TRY
		SELECT	@sCommand = 'USE [' + @database_name + ']; DBCC CHECKDB WITH NO_INFOMSGS;'
		EXEC	(@sCommand)
	END TRY
	BEGIN CATCH
		--- Log the error - if possible
		DECLARE @sResults varchar(8000)
		SELECT @sResults = 'The database ' + @database_name + ' failed to perform maintenance (' + @sCommand + ').'
		IF ERROR_NUMBER() IS NOT NULL
			SELECT @sResults = @sResults + CHAR(13) 
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

		--- Print the error message to sysout
		PRINT @sResults

		--- Insert the error in the database
		INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database_name, GETDATE(), 'checkdb', 0, @sResults)
	END CATCH
  END
ELSE
	PRINT 'The database ' + @database_name + ' is not in ONLINE mode or is READ-ONLY'

SET NOCOUNT OFF
